# -*- coding: utf-8 -*-
"""
Intangible‑cultural‑heritage sports projects: DBSCAN density‑based clustering and conversion‑rate analysis
SCI‑level plotting with English‑language figures

Package installation:
    pip install pandas openpyxl matplotlib wordcloud numpy pillow pypinyin scikit-learn

Instructions: Ensure the file "Summary_Dataset_ICH_Sports_Uni_Courses.xlsx"
    is placed in your working directory before running the script.
"""
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
from wordcloud import WordCloud
import numpy as np
import os
import re

# Scikit‑learn modules for text vectorization, DBSCAN clustering and cluster evaluation
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.cluster import DBSCAN
from sklearn.metrics import silhouette_score

# Import pypinyin for romanization; fall‑back to original Chinese if unavailable
try:
    from pypinyin import lazy_pinyin
    PINYIN_AVAILABLE = True
except ImportError:
    PINYIN_AVAILABLE = False
    print("⚠️ Warning: pypinyin not installed. Word‑cloud will display Chinese text. Install: pip install pypinyin")

# Global matplotlib settings for SCI‑style serif font (Times New Roman)
plt.rcParams['font.family'] = 'serif'
plt.rcParams['font.serif'] = ['Times New Roman', 'DejaVu Serif']
plt.rcParams['axes.unicode_minus'] = False

# ==================== Helper function: Convert Chinese text to Pinyin ====================
def chinese_to_pinyin(text):
    """
    Convert Chinese project name to capitalized Pinyin string
    Parenthesized annotations are removed before conversion
    """
    if not PINYIN_AVAILABLE:
        return text
    if not text:
        return ''
    # Strip content inside round‑brackets
    clean_text = re.sub(r'[（(].*?[）)]', '', text).strip()
    pinyin_list = lazy_pinyin(clean_text)
    # Capitalize each syllable and concatenate
    return ''.join([p.capitalize() for p in pinyin_list])


# ==================== 1. Data loading and pre‑processing ====================
def load_data(file_path):
    df = pd.read_excel(file_path, sheet_name='汇总')
    df = df.iloc[:, [2, 4]].copy()
    df.columns = ['Project_Name', 'Host_Universities']
    df = df.dropna(subset=['Project_Name'])
    df = df.drop_duplicates(subset=['Project_Name'])
    # Binary flag: 1 = course offered, 0 = no university offering this project
    df['Course_Offered'] = df['Host_Universities'].apply(lambda x: 0 if pd.isna(x) or str(x).strip() == '无' else 1)

    def count_schools(text):
        """Count the number of host universities from delimited text string"""
        if pd.isna(text) or str(text).strip() == '无':
            return 0
        return len(str(text).split('、')) if '、' in str(text) else len(str(text).split('，'))

    df['University_Count'] = df['Host_Universities'].apply(count_schools)
    return df


# ==================== 2. DBSCAN unsupervised clustering (TF‑IDF text features) ====================
def dbscan_cluster_projects(df, eps=0.72, min_samples=2):
    """
    Perform density‑based clustering on ICH project names using TF‑IDF character features
    :param df: Input dataframe
    :param eps: Neighborhood radius (cosine distance for text; suggested 0.65‑0.85)
    :param min_samples: Minimum number of samples required to form a dense cluster
    :return: Dataframe appended with cluster‑label columns
    """
    project_texts = df['Project_Name'].tolist()
    # TF‑IDF feature extraction using character‑level n‑grams
    tfidf = TfidfVectorizer(analyzer='char', ngram_range=(1, 3))
    X = tfidf.fit_transform(project_texts)

    db = DBSCAN(eps=eps, min_samples=min_samples, metric='cosine')
    cluster_labels = db.fit_predict(X)
    df['Cluster_Label'] = cluster_labels

    # Print clustering summary
    n_clusters = len(set(cluster_labels)) - (1 if -1 in cluster_labels else 0)
    n_noise = list(cluster_labels).count(-1)
    print(f"\n===== DBSCAN Clustering Summary =====")
    print(f"Parameters: eps={eps}, min_samples={min_samples}")
    print(f"Number of detected clusters: {n_clusters}")
    print(f"Number of noise (outlier) samples: {n_noise}")

    # Compute silhouette score only for non‑noise points
    valid_idx = np.where(cluster_labels != -1)[0]
    if len(np.unique(cluster_labels[valid_idx])) >= 2:
        sil_score = silhouette_score(X[valid_idx], cluster_labels[valid_idx])
        print(f"Silhouette Score = {sil_score:.4f}")
    else:
        print("Effective clusters < 2, silhouette‑score skipped. Try lowering eps or min_samples.")

    # Generate readable cluster names; label‑1 is marked as Noise
    def get_cluster_name(label):
        if label == -1:
            return "Noise"
        else:
            return f"Cluster_{label}"

    df['Cluster_Name'] = df['Cluster_Label'].apply(get_cluster_name)
    return df


# ==================== 3. Generate conversion‑rate statistics grouped by cluster ====================
def compute_conversion(df):
    stats = df.groupby('Cluster_Name').agg(
        Total_Projects=('Project_Name', 'count'),
        Offered_Projects=('Course_Offered', 'sum'),
        Project_List=('Project_Name', lambda x: '、'.join(x))
    ).reset_index()
    stats['Conversion_Rate_Pct'] = (stats['Offered_Projects'] / stats['Total_Projects'] * 100).round(2)
    stats = stats.sort_values('Conversion_Rate_Pct', ascending=False)
    stats.insert(0, 'Cluster_ID', range(1, len(stats) + 1))
    stats['Cluster_Name_EN'] = stats['Cluster_Name']
    return stats


def save_statistics(stats, output_file='b.DBSCAN_Cluster_Conversion_Stats.xlsx'):
    """Export cluster‑wise conversion‑rate table to formatted Excel workbook"""
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        stats.to_excel(writer, sheet_name='DBSCAN_Cluster_Stats', index=False)
        worksheet = writer.sheets['DBSCAN_Cluster_Stats']
        for col in worksheet.columns:
            max_len = max(len(str(cell.value)) for cell in col)
            worksheet.column_dimensions[col[0].column_letter].width = min(max_len + 2, 30)
    print(f"✅ Statistics table saved: {output_file}")


# ==================== 4. Bar‑chart plotting (English labels, SCI format) ====================
def plot_bar_chart(stats, avg_conversion, output_file='c.DBSCAN_Conversion_Rate_Bar_Chart.png'):
    fig, ax = plt.subplots(figsize=(10, 6))
    categories = stats['Cluster_Name_EN']
    values = stats['Conversion_Rate_Pct']
    bars = ax.bar(categories, values, color='#4A7B9D', edgecolor='white', linewidth=1)

    for bar, val in zip(bars, values):
        ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + 0.5, f'{val:.1f}%',
                ha='center', va='bottom', fontsize=11, fontweight='bold')

    ax.axhline(y=avg_conversion, color='#D95F02', linestyle='--', linewidth=2,
               label=f'Overall Avg: {avg_conversion:.1f}%')
    ax.legend(loc='upper right', frameon=False)

    ax.set_xlabel('DBSCAN Cluster', fontsize=13)
    ax.set_ylabel('University Course Conversion Rate (%)', fontsize=13)
    ax.set_title('Conversion Rate of ICH Traditional Sports by DBSCAN Cluster', fontsize=15, fontweight='bold')
    ax.set_ylim(0, 85)
    ax.grid(axis='y', linestyle=':', alpha=0.4)
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)

    plt.tight_layout()
    plt.savefig(output_file, dpi=300, bbox_inches='tight')
    plt.savefig(output_file.replace('.png', '.pdf'), bbox_inches='tight')
    print(f"✅ Bar‑chart saved: {output_file} and PDF version")


# ==================== 5. Word‑cloud subplots (Pinyin labels, grouped by cluster) ====================
def plot_cloud_by_cluster(df, output_file='a.DBSCAN_WordCloud_ICH_Clusters.png'):
    """Generate multi‑panel word‑cloud, font‑size proportional to university offering count"""
    clusters = sorted(df['Cluster_Name'].unique())
    n_cls = len(clusters)
    cols = min(3, n_cls)
    rows = (n_cls + cols - 1) // cols

    fig, axes = plt.subplots(rows, cols, figsize=(16, rows * 5))
    if n_cls == 1:
        axes = [axes]
    else:
        axes = axes.flatten()

    for idx, cls_name in enumerate(clusters):
        ax = axes[idx]
        sub_df = df[df['Cluster_Name'] == cls_name]

        freq_dict = dict(zip(sub_df['Project_Name'], sub_df['University_Count']))
        freq_dict = {k: max(1, v) for k, v in freq_dict.items()}
        eng_freq_dict = {chinese_to_pinyin(k): v for k, v in freq_dict.items()}

        if not eng_freq_dict:
            ax.text(0.5, 0.5, 'No Data', ha='center', va='center', fontsize=16)
            ax.set_title(cls_name, fontsize=14)
            continue

        try:
            wc = WordCloud(
                background_color='white',
                width=600, height=400,
                max_words=50,
                colormap='Blues',
                relative_scaling=0.5,
                random_state=42
            ).generate_from_frequencies(eng_freq_dict)
            ax.imshow(wc, interpolation='bilinear')
        except Exception as e:
            ax.text(0.1, 0.5, '\n'.join(eng_freq_dict.keys()), ha='left', va='center', fontsize=10)
            print(f"⚠️ Warning: Word‑cloud generation failed for cluster '{cls_name}': {e}")

        ax.axis('off')
        ax.set_title(cls_name, fontsize=16, fontweight='bold')

    # Hide unused subplot panels
    for idx in range(len(clusters), len(axes)):
        axes[idx].axis('off')

    plt.suptitle('Word Cloud of ICH Traditional Sports DBSCAN Clusters (Font Size ∝ Number of Universities Offering Courses)',
                 fontsize=16, y=1.02)
    plt.tight_layout()
    plt.savefig(output_file, dpi=300, bbox_inches='tight')
    plt.savefig(output_file.replace('.png', '.pdf'), bbox_inches='tight')
    print(f"✅ Word‑cloud figure saved: {output_file} and PDF version")


# ==================== Main execution workflow ====================
def main():
    input_file = '全国非遗传统体育项目高校开课情况最终汇总表.xlsx'
    if not os.path.exists(input_file):
        print(f"❌ Error: Input dataset {input_file} not found in working directory.")
        return

    # Step 1: Load raw dataset
    df = load_data(input_file)
    print(f"✅ Loaded total projects: {len(df)}")
    total_projects = len(df)
    total_open = df['Course_Offered'].sum()
    avg_conversion = total_open / total_projects * 100
    print(f"   Total ICH projects: {total_projects}, Courses offered: {total_open}, Overall conversion rate: {avg_conversion:.2f}%")

    # Step 2: Run DBSCAN clustering, adjust eps / min_samples here
    df = dbscan_cluster_projects(df, eps=0.72, min_samples=2)
    print("✅ DBSCAN density‑clustering completed")

    # Step 3: Calculate cluster‑wise conversion‑rate metrics
    stats = compute_conversion(df)
    print("✅ Conversion‑rate statistics computed")
    print(stats[['Cluster_Name', 'Total_Projects', 'Offered_Projects', 'Conversion_Rate_Pct']])

    # Step 4: Export statistical table
    save_statistics(stats)

    # Step 5: Plot bar‑chart for conversion‑rate comparison
    plot_bar_chart(stats, avg_conversion)

    # Step 6: Generate multi‑cluster word‑cloud visualization
    plot_cloud_by_cluster(df)

    print("\n🎉 Analysis finished! Output files:")
    print("   - b.DBSCAN_Cluster_Conversion_Stats.xlsx")
    print("   - c.DBSCAN_Conversion_Rate_Bar_Chart.png/pdf")
    print("   - a.DBSCAN_WordCloud_ICH_Clusters.png/pdf")


if __name__ == '__main__':
    main()
